package com.zsgeek.wisdchain.qutu.base.SQL;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteConstraintException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.widget.Toast;

import com.zsgeek.wisdchain.qutu.base.model.WdcAllWalletTable;
import com.zsgeek.wisdchain.qutu.tools.LogUtil;

import java.util.ArrayList;
import java.util.List;


public class QuTuDao {
    //    字段定义
    private final String CoinAddress = "CoinAddress", Keystore = "Keystore", KeyName = "KeyName";
    // 列定义
    private final String[] ORDER_COLUMNS = new String[]{"Id", KeyName, CoinAddress, Keystore};

    private Context context;
    private QuTuDBHelper ordersDBHelper;

    public QuTuDao(Context context, String tableName) {
        this.context = context;
        ordersDBHelper = new QuTuDBHelper(context, tableName);
    }

    /**
     * 判断表中是否有数据
     * 调用如下：
     * if (!marketSearchDao.isDataExist(tableName)) {
     * marketSearchDao.initTable(tableName);
     * }
     *
     * @param tableName 表名  （TABLE_NAME_MarketSearchTableUSDT，TABLE_NAME_MarketSearchTableBTC，TABLE_NAME_MarketSearchTableETH）
     * @return
     */
    public boolean isDataExist(String tableName) {
        int count = 0;

        SQLiteDatabase db = null;
        Cursor cursor = null;

        try {
            db = ordersDBHelper.getReadableDatabase();
            // select count(Id) from Orders
            cursor = db.query(tableName, new String[]{"COUNT(Id)"}, null, null, null, null, null);

            if (cursor.moveToFirst()) {
                count = cursor.getInt(0);
            }
            if (count > 0) return true;
        } catch (SQLiteException e) {
            e.printStackTrace();
            if (e.getMessage().contains("no such table")) {
                createTable(tableName);
                LogUtil.e(tableName + "不存在，重新创建");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (db != null) {
                db.close();
            }
        }
        return false;
    }

    /**
     * 初始化数据(当一开始if (! ordersDao.isDataExist())为空的时候可使用此方法往数据库插入内置数据)
     */
    public void initTable(String tableName) {
        SQLiteDatabase db = null;
        try {
            db = ordersDBHelper.getWritableDatabase();
            db.beginTransaction();
            db.execSQL("insert into " + tableName + " (Id, KeyName,CoinAddress,Keystore)" +
                    " values (1,'BTC', '5111.01')");
            db.execSQL("insert into " + tableName + " (Id, KeyName,CoinAddress,Keystore)" +
                    " values (2, 'ETH', '167.89')");
            db.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (db != null) {
                db.endTransaction();
                db.close();
            }
        }
    }

    /**
     * 执行自定义SQL语句
     */
    public boolean execSQL(String sql) {
        SQLiteDatabase db = null;
        boolean resoult = false;
        try {
            if (sql.contains("select")) {
                LogUtil.i("Sorry，还没处理select语句");
                resoult = false;
            } else if (sql.contains("insert") || sql.contains("update") || sql.contains("delete") || sql.contains("drop")) {
                db = ordersDBHelper.getWritableDatabase();
                db.beginTransaction();
                db.execSQL(sql);
                db.setTransactionSuccessful();
                LogUtil.i("执行SQL语句成功");
                resoult = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
            resoult = false;
            LogUtil.i("执行出错，请检查SQL语句");
        } finally {
            if (db != null) {
                db.endTransaction();
                db.close();
            }
        }
        return resoult;
    }

    /**
     * 清空某一表
     */
    public void deleteTable(String tableName) {
        try {
            SQLiteDatabase db = ordersDBHelper.getWritableDatabase();
            db.execSQL("delete from " + tableName);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //删除某一个表
    public void dropTable(String tableName) {
        try {
            SQLiteDatabase db = ordersDBHelper.getWritableDatabase();
            db.execSQL("drop table " + tableName);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 查询数据库中所有数据
     */
    public List<WdcAllWalletTable> getAllDate(String tableName) {
        if (tableName == null)
            tableName = QuTuDBHelper.TABLE_NAME_MarketSearchTable;
        SQLiteDatabase db = null;
        Cursor cursor = null;

        try {
            db = ordersDBHelper.getReadableDatabase();
            // select * from Orders
            cursor = db.query(tableName, ORDER_COLUMNS, null, null, null, null, null);

            if (cursor.getCount() > 0) {
                List<WdcAllWalletTable> orderList = new ArrayList<>(cursor.getCount());
                while (cursor.moveToNext()) {
                    orderList.add(parseOrder(cursor));
                }
                return orderList;
            }
        } catch (SQLiteException e) {
            e.printStackTrace();
            if (e.getMessage().contains("no such table")) {
                createTable(tableName);
                LogUtil.e(tableName + "不存在，重新创建");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (db != null) {
                db.close();
            }
        }

        return null;
    }

    private void createTable(String tableName) {
        try {
            SQLiteDatabase db = ordersDBHelper.getWritableDatabase();
            db.execSQL(ordersDBHelper.createTable(tableName));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 新增一条数据
     *
     * @id null时代表自增
     * 返回主键ID
     */
    public long insertDate(String tableName, String keyName, String coinAddress, String keystore) {
        SQLiteDatabase db = null;
        long insertId = -1;
        try {
            db = ordersDBHelper.getWritableDatabase();
            db.beginTransaction();

            // insert into Orders(Id, CustomName, OrderPrice, Country) values (7, "Jne", 700, "China");
            ContentValues contentValues = new ContentValues();
//            contentValues.put("Id", dataListBean.id); //不写则ID自增
            contentValues.put(CoinAddress, coinAddress);
            contentValues.put(Keystore, keystore);
            contentValues.put(KeyName, keyName);
            insertId = db.insertOrThrow(tableName, null, contentValues);

            db.setTransactionSuccessful();
            return insertId;
        } catch (SQLiteConstraintException e) {
            Toast.makeText(context, "主键重复", Toast.LENGTH_SHORT).show();
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (db != null) {
                db.endTransaction();
                db.close();
            }
        }
        return insertId;
    }

    /**
     * 删除该ID数据
     */
    public boolean deleteOrder(String tableName, int id) {
        SQLiteDatabase db = null;

        try {
            db = ordersDBHelper.getWritableDatabase();
            db.beginTransaction();

            // delete from Orders where Id = 7
            db.delete(tableName, "Id = ?", new String[]{String.valueOf(id)});
            db.setTransactionSuccessful();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (db != null) {
                db.endTransaction();
                db.close();
            }
        }
        return false;
    }

    /**
     * 修改一条数据  此处将Id为6的数据的SortTag修改成800
     *
     * @numberType 需要强转类型 0 integer 1 string
     * @number 可传int 或者string 强转
     */
    public boolean updateOrder(String tableName, int id, int numberType, Object number) {
        SQLiteDatabase db = null;
        try {
            db = ordersDBHelper.getWritableDatabase();
            db.beginTransaction();

            // update Orders set OrderPrice = 800 where Id = 6
            ContentValues cv = new ContentValues();
            if (numberType == 0)
                cv.put(KeyName, (int) number);
            else if (numberType == 1)
                cv.put(KeyName, (String) number);
            db.update(tableName,
                    cv,
                    "Id = ?",
                    new String[]{String.valueOf(id)});
            db.setTransactionSuccessful();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (db != null) {
                db.endTransaction();
                db.close();
            }
        }

        return false;
    }

    /**
     * 数据查询  此处将用户名为"Bor"的信息提取出来
     */
    public String getKeystoreByAddress(String coinAddress) {
        SQLiteDatabase db = null;
        Cursor cursor = null;

        try {
            db = ordersDBHelper.getReadableDatabase();

            cursor = db.rawQuery("select Keystore from " + QuTuDBHelper.TABLE_NAME_MarketSearchTable + " where CoinAddress=?",
                    new String[]{coinAddress}
            );

            if (cursor.getCount() > 0) {
                if (cursor.moveToFirst()) {
                    return cursor.getString(cursor.getColumnIndex("Keystore"));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (db != null) {
                db.close();
            }
        }

        return null;
    }

    /**
     * 统计查询  此处查询Country为China的用户总数
     */
    public int getChinaCount(String tableName) {
        int count = 0;

        SQLiteDatabase db = null;
        Cursor cursor = null;

        try {
            db = ordersDBHelper.getReadableDatabase();
            // select count(Id) from Orders where Country = 'China'
            cursor = db.query(tableName,
                    new String[]{"COUNT(Id)"},
                    "Country = ?",
                    new String[]{"China"},
                    null, null, null);

            if (cursor.moveToFirst()) {
                count = cursor.getInt(0);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (db != null) {
                db.close();
            }
        }

        return count;
    }

    /**
     * 比较查询  此处查询单笔数据中OrderPrice最高的
     */
//    public TradeTokenListModel.DataBean.DataListBean getMaxOrderPrice(String tableName) {
//        SQLiteDatabase db = null;
//        Cursor cursor = null;
//
//        try {
//            db = ordersDBHelper.getReadableDatabase();
//            // select Id, CustomName, Max(OrderPrice) as OrderPrice, Country from Orders
//            cursor = db.query(tableName, new String[]{"Id", "CustomName", "Max(OrderPrice) as OrderPrice", "Country"}, null, null, null, null, null);
//
//            if (cursor.getCount() > 0) {
//                if (cursor.moveToFirst()) {
//                    return parseOrder(cursor);
//                }
//            }
//        } catch (Exception e) {
//            e.printStackTrace();
//        } finally {
//            if (cursor != null) {
//                cursor.close();
//            }
//            if (db != null) {
//                db.close();
//            }
//        }
//
//        return null;
//    }

    /**
     * 将查找到的数据转换成MarketSearchModel.DataBean.DataListBean类
     */
    private WdcAllWalletTable parseOrder(Cursor cursor) {
        WdcAllWalletTable order = new WdcAllWalletTable();
        order.setId(cursor.getInt(cursor.getColumnIndex("Id")));
        order.setKeyName(cursor.getString(cursor.getColumnIndex(KeyName)));
        order.setCoinAddress(cursor.getString(cursor.getColumnIndex(CoinAddress)));
        order.setKeystore(cursor.getString(cursor.getColumnIndex(Keystore)));
        return order;
    }

    /**
     * 判断数据库中某张表是否存在
     */
    public boolean sqlTableIsExist(String tableName) {
        boolean result = false;
        if (tableName == null) {
            return false;
        }
        SQLiteDatabase db = null;
        Cursor cursor = null;
        try {
            //search.db数据库的名字
            db = ordersDBHelper.getWritableDatabase();
            String sql = "select count(*) as c from Sqlite_master  where type ='table' and name ='" + tableName.trim() + "' ";
            cursor = db.rawQuery(sql, null);
            if (cursor.moveToNext()) {
                int count = cursor.getInt(0);
                if (count > 0) {
                    result = true;
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
            // TODO: handle exception
        }
        return result;
    }
}
